package org.emet.dao.storage;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.List;

import org.emet.service.dto.storage.RetailReportGroupDateEntry;
import org.emet.service.dto.storage.RetailReportItemGroupEntry;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class RetailReportDao {

	private SimpleJdbcTemplate jdbcTemplate;

	public SimpleJdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(SimpleJdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public List<RetailReportItemGroupEntry> getReportGroupByItem(java.sql.Date start, java.sql.Date end) {
		final HashMap<String, Object> paramMap = new HashMap<String, Object>();
		String sql = buildGroupItemSql(start, end, paramMap);
		return jdbcTemplate.query(sql, groupItemMapper, paramMap);
	}

	public List<RetailReportGroupDateEntry> queryGroupByDay(java.sql.Date start, java.sql.Date end) {
		final HashMap<String, Object> paramMap = new HashMap<String, Object>();

		String sql = buildGroupDaySql(start, end, paramMap);

		return jdbcTemplate.query(sql, groupDayMapper, paramMap);
	}

	public List<RetailReportGroupDateEntry> queryGroupByMonth(int startYear, int startMonth, int endYear, int endMonth) {
		final HashMap<String, Object> paramMap = new HashMap<String, Object>();

		String sql = buildGroupMonthSql(startYear, startMonth, endYear, endMonth, paramMap);

		return jdbcTemplate.query(sql, groupMonthMapper, paramMap);
	}

	public List<RetailReportGroupDateEntry> queryGroupByYear(int startYear, int endYear) {
		final HashMap<String, Object> paramMap = new HashMap<String, Object>();

		String sql = buildGroupYearSql(startYear, endYear, paramMap);

		return jdbcTemplate.query(sql, groupYearMapper, paramMap);
	}

	private String buildGroupDaySql(java.sql.Date start, java.sql.Date end, final HashMap<String, Object> paramMap) {
		final boolean hasStartDate = start != null;
		final boolean hasEndDate = end != null;

		String sql = " select pp.date as p_date,ss.date as s_date,s_amount,p_amount from "
				+ "	(SELECT s.date,sum(s.amount) as s_amount FROM t_sale_item s where 1=1 ";

		if (hasStartDate) {
			sql += " and s.date>=:startDate ";
			paramMap.put("startDate", start);
		}

		if (hasEndDate) {
			sql += " and s.date<=:endDate ";
			paramMap.put("endDate", end);
		}

		sql += " group by s.date) as ss " + "	left join "
				+ "	(SELECT p.date,sum(p.amount) as p_amount FROM t_purchase_item p where 1=1 ";

		if (hasStartDate) {
			sql += " and p.date>=:startDate ";
		}

		if (hasEndDate) {
			sql += " and p.date<=:endDate ";
		}

		sql += " group by p.date) as pp " + "	on ss.date = pp.date union";

		sql += " select pp.date as p_date,ss.date as s_date,s_amount,p_amount from "
				+ "	(SELECT s.date,sum(s.amount) as s_amount FROM t_sale_item s where 1=1 ";

		if (hasStartDate) {
			sql += " and s.date>=:startDate ";
		}

		if (hasEndDate) {
			sql += " and s.date<=:endDate ";
		}

		sql += " group by s.date) as ss " + "	right join "
				+ "	(SELECT p.date,sum(p.amount) as p_amount FROM t_purchase_item p where 1=1 ";

		if (hasStartDate) {
			sql += " and p.date>=:startDate ";
		}

		if (hasEndDate) {
			sql += " and p.date<=:endDate ";
		}

		sql += " group by p.date) as pp " + "	on ss.date = pp.date order by p_date,s_date desc";

		return sql;
	}

	private String buildGroupMonthSql(int startYear, int startMonth, int endYear, int endMonth,
			final HashMap<String, Object> paramMap) {
		final boolean hasStartDate = startYear > 0;
		final boolean hasEndDate = endYear > 0;

		String sql = " select s_year,s_month,sum(s_amount) as s_amount,sum(p_amount) as p_amount from ( "
				+ "  select year(ss.date) as s_year,month(ss.date) as s_month,s_amount,p_amount from "
				+ "	(SELECT s.date,sum(s.amount) as s_amount FROM t_sale_item s where 1=1 ";

		if (hasStartDate) {
			sql += " and year(s.date)>=:startYear and month(s.date) >= :startMonth";
			paramMap.put("startYear", startYear);
			paramMap.put("startMonth", startMonth);
		}

		if (hasEndDate) {
			sql += " and year(s.date) <= :endYear and month(s.date) <= :endMonth ";
			paramMap.put("endYear", endYear);
			paramMap.put("endMonth", endMonth);
		}

		sql += " group by year(s.date),month(s.date)) as ss " + "	left join "
				+ "	(SELECT p.date,sum(p.amount) as p_amount FROM t_purchase_item p where 1=1 ";

		if (hasStartDate) {
			sql += " and year(p.date)>=:startYear and month(p.date) >= :startMonth";
		}

		if (hasEndDate) {
			sql += " and year(p.date) <= :endYear and month(p.date) <= :endMonth";
		}

		sql += " group by year(p.date),month(p.date)) as pp " + "	on ss.date = pp.date union";

		sql += " select year(pp.date) as s_year,month(pp.date) as s_year,s_amount,p_amount from "
				+ "	(SELECT s.date,sum(s.amount) as s_amount FROM t_sale_item s where 1=1 ";

		if (hasStartDate) {
			sql += " and year(s.date)>=:startYear and month(s.date) >= :startMonth";
		}

		if (hasEndDate) {
			sql += " and year(s.date) <= :endYear and month(s.date) <= :endMonth ";
		}

		sql += " group by year(s.date),month(s.date)) as ss " + "	right join "
				+ "	(SELECT p.date,sum(p.amount) as p_amount FROM t_purchase_item p where 1=1 ";

		if (hasStartDate) {
			sql += " and year(p.date)>=:startYear and month(p.date) >= :startMonth";
		}

		if (hasEndDate) {
			sql += " and year(p.date) <= :endYear and month(p.date) <= :endMonth";
		}

		sql += " group by year(p.date),month(p.date)) as pp "
				+ "	on ss.date = pp.date ) as whole_table group by s_year,s_month order by s_year,s_month desc";

		return sql;
	}

	private String buildGroupYearSql(int startYear, int endYear, final HashMap<String, Object> paramMap) {
		final boolean hasStartDate = startYear > 0;
		final boolean hasEndDate = endYear > 0;

		String sql = " select s_year,sum(s_amount) as s_amount,sum(p_amount) as p_amount from ( "
				+ "  select year(ss.date) as s_year, s_amount,p_amount from "
				+ "	(SELECT s.date,sum(s.amount) as s_amount FROM t_sale_item s where 1=1 ";

		if (hasStartDate) {
			sql += " and year(s.date)>=:startYear ";
			paramMap.put("startYear", startYear);
		}

		if (hasEndDate) {
			sql += " and year(s.date) <= :endYear ";
			paramMap.put("endYear", endYear);
		}

		sql += " group by year(s.date) ) as ss " + "	left join "
				+ "	(SELECT p.date,sum(p.amount) as p_amount FROM t_purchase_item p where 1=1 ";

		if (hasStartDate) {
			sql += " and year(p.date)>=:startYear ";
		}

		if (hasEndDate) {
			sql += " and year(p.date) <= :endYear  ";
		}

		sql += " group by year(p.date) ) as pp " + "	on ss.date = pp.date union";

		sql += " select year(pp.date) as s_year ,s_amount,p_amount from "
				+ "	(SELECT s.date,sum(s.amount) as s_amount FROM t_sale_item s where 1=1 ";

		if (hasStartDate) {
			sql += " and year(s.date)>=:startYear ";
		}

		if (hasEndDate) {
			sql += " and year(s.date) <= :endYear  ";
		}

		sql += " group by year(s.date) ) as ss " + "	right join "
				+ "	(SELECT p.date,sum(p.amount) as p_amount FROM t_purchase_item p where 1=1 ";

		if (hasStartDate) {
			sql += " and year(p.date)>=:startYear ";
		}

		if (hasEndDate) {
			sql += " and year(p.date) <= :endYear  ";
		}

		sql += " group by year(p.date),month(p.date)) as pp "
				+ "	on ss.date = pp.date ) as whole_table group by s_year order by s_year desc";

		return sql;
	}

	public String buildGroupItemSql(java.sql.Date start, java.sql.Date end, final HashMap<String, Object> paramMap) {
		final boolean hasStartDate = start != null;
		final boolean hasEndDate = end != null;

		String sql = " select date,sum(p_amount) as p_amount,sum(s_amount) as s_amount,item_id,code,name,unit,sum(p_count) as p_count,sum(s_count) as s_count from( "
				+ " SELECT s.date,0 as p_amount,sum(s.amount) as s_amount,s.item_id,t_item.code,t_item.name,t_item.unit,0 as p_count,sum(s.item_count) as s_count FROM t_sale_item s left join t_item on t_item.id = s.item_id  group by s.item_id having 1=1 ";
		if (hasStartDate) {
			sql += " and s.date>=:startDate ";
			paramMap.put("startDate", start);
		}

		if (hasEndDate) {
			sql += " and s.date<=:endDate ";
			paramMap.put("endDate", end);
		}
		sql += " union SELECT p.date,sum(p.amount) as p_amount,0,p.item_id,t_item.code,t_item.name,t_item.unit,sum(p.item_count) as p_count,0 as s_count FROM t_purchase_item p  left join t_item on t_item.id = p.item_id group by p.item_id having 1=1 ";
		if (hasStartDate) {
			sql += " and p.date>=:startDate ";
		}

		if (hasEndDate) {
			sql += " and p.date<=:endDate ";
		}
		sql += " ) as reslut_set group by item_id ";
		return sql;
	}

	private ParameterizedRowMapper<RetailReportGroupDateEntry> groupDayMapper = new ParameterizedRowMapper<RetailReportGroupDateEntry>() {
		public RetailReportGroupDateEntry mapRow(ResultSet rs, int rowNum) throws SQLException {
			RetailReportGroupDateEntry entry = new RetailReportGroupDateEntry();
			Date date = (rs.getDate("p_date") == null) ? rs.getDate("s_date") : rs.getDate("p_date");
			entry.setDate(new SimpleDateFormat("yyy-MM-dd").format(date));
			entry.setPurchaseAmount(rs.getBigDecimal("p_amount"));
			entry.setSaleAmount(rs.getBigDecimal("s_amount"));
			return entry;
		}
	};
	private ParameterizedRowMapper<RetailReportGroupDateEntry> groupMonthMapper = new ParameterizedRowMapper<RetailReportGroupDateEntry>() {
		public RetailReportGroupDateEntry mapRow(ResultSet rs, int rowNum) throws SQLException {
			RetailReportGroupDateEntry entry = new RetailReportGroupDateEntry();
			String yearMonthStr = rs.getString("s_year") + "-" + rs.getString("s_month");
			entry.setDate(yearMonthStr);
			entry.setPurchaseAmount(rs.getBigDecimal("p_amount"));
			entry.setSaleAmount(rs.getBigDecimal("s_amount"));
			return entry;
		}
	};
	private ParameterizedRowMapper<RetailReportGroupDateEntry> groupYearMapper = new ParameterizedRowMapper<RetailReportGroupDateEntry>() {
		public RetailReportGroupDateEntry mapRow(ResultSet rs, int rowNum) throws SQLException {
			RetailReportGroupDateEntry entry = new RetailReportGroupDateEntry();
			entry.setDate(rs.getString("s_year"));
			entry.setPurchaseAmount(rs.getBigDecimal("p_amount"));
			entry.setSaleAmount(rs.getBigDecimal("s_amount"));
			return entry;
		}
	};

	private ParameterizedRowMapper<RetailReportItemGroupEntry> groupItemMapper = new ParameterizedRowMapper<RetailReportItemGroupEntry>() {
		public RetailReportItemGroupEntry mapRow(ResultSet rs, int rowNum) throws SQLException {
			RetailReportItemGroupEntry entry = new RetailReportItemGroupEntry();
			entry.setCode(rs.getString("code"));
			entry.setItem(rs.getString("name"));
			entry.setPurchaseAmount(rs.getBigDecimal("p_amount"));
			entry.setPurchaseCount(rs.getLong("p_count"));
			entry.setSaleAmount(rs.getBigDecimal("s_amount"));
			entry.setSaleCount(rs.getLong("s_count"));
			entry.setUnit(rs.getString("unit"));
			return entry;
		}
	};
}
